use serde::Serialize;
use sqlx::Executor;
use sqlx::Row;
use sqlx::SqliteConnection;

use crate::error::{AppErr, SqlxErr, UniqueMap};

use super::get_pool;

const CREATE_SQL: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_device (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        type_id INTEGER NOT NULL, 
        mac_addr TEXT NOT NULL, 
        name TEXT NOT NULL, 
        remark TEXT NOT NULL, 
        UNIQUE (mac_addr)
    )
"#;

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

pub async fn create(type_id: i64, mac_addr: &str, name: &str, remark: &str) -> Result<i64, AppErr> {
    let ret = sqlx::query(
        r#"INSERT INTO tb_device 
        (type_id, mac_addr, name, remark) 
        VALUES (?, ?, ?, ?)"#,
    )
    .bind(type_id)
    .bind(mac_addr)
    .bind(name)
    .bind(remark)
    .execute(get_pool())
    .await
    .map_unique("设备MAC地址重读")?;
    Ok(ret.last_insert_rowid())
}

pub async fn update(id: i64, name: &str, remark: &str) -> Result<(), SqlxErr> {
    sqlx::query("UPDATE tb_device SET name = ?, remark = ? WHERE id = ?")
        .bind(name)
        .bind(remark)
        .bind(id)
        .execute(get_pool())
        .await?;
    Ok(())
}

pub async fn is_exist(mac_addr: &str) -> Result<Option<i64>, SqlxErr> {
    let ret = sqlx::query("SELECT id FROM tb_device WHERE mac_addr = ? LIMIT 1")
        .bind(mac_addr)
        .fetch_optional(get_pool())
        .await?;
    Ok(ret.and_then(|row| row.get(0)))
}

#[derive(Debug, Serialize)]
pub struct TbDevice {
    id: i64,
    mac_addr: String,
    name: String,
    remark: String,
}

pub async fn query(type_id: i64) -> Result<Box<[TbDevice]>, SqlxErr> {
    let rows = sqlx::query(r#"
        SELECT id, mac_addr, name, remark FROM tb_device 
        WHERE type_id = ?
    "#).bind(type_id).fetch_all(get_pool()).await?;
    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let item = TbDevice {
            id: row.get(0),
            mac_addr: row.get(1),
            name: row.get(2),
            remark: row.get(3)
        };
        vec.push(item);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn delete(id: i64) -> Result<(), SqlxErr> {
    sqlx::query("DELETE FROM tb_device WHERE id = ?").bind(id).execute(get_pool()).await?;
    Ok(())
}

pub async fn delete_with_type(type_id: i64, conn: &mut SqliteConnection) -> Result<(), SqlxErr> {
    sqlx::query("DELETE FROM tb_device WHERE type_id = ?").bind(type_id).execute(conn).await?;
    Ok(())
}


